package noobteam.deartrainer.mapper;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CourseSelectionsMapper {

    /**
     * 学员选课
     */
    @Insert("INSERT INTO courseselections (userId, courseId, paidDate) " +
            "VALUES (#{js.userId}, #{js.courseId}, CURDATE())")
    public boolean selectCourse(@Param("js") Map<String, String> map);

    /**
     * 学员退选
     */
    @Delete("DELETE FROM courseselections WHERE userId = #{js.userId} AND courseId = #{js.courseId}")
    public boolean dropCourse(@Param("js") Map<String, String> map);


    /**
     * 删除学员
     * 删除该用户的所有选课记录
     */
    @Delete("DELETE FROM courseselections WHERE userId = #{userId}")
    public boolean deleteStudent(@Param("userId") Integer userId);

    /**
     * 删除课程
     * 删除该课程的所有选课记录
     */
    @Delete("DELETE FROM courseselections WHERE courseId = #{courseId}")
    public boolean deleteCourse(@Param("courseId") Integer courseId);

    /**
     * 获取某课程的选课人数
     */
    @Select("SELECT COUNT(*) FROM courseselections WHERE courseId = #{courseId}")
    public Integer getCourseSelectedNumber(@Param("courseId") Integer courseId);

    /**
     * 获取学员选课数量
     */
    @Select("SELECT COUNT(*) FROM courseselections WHERE userId = #{userId}")
    public Integer getStudentSelectionsNumber(@Param("userId") Integer userId);

    /**
     * 获取学员选择的课程信息列表
     */
    @Select("SELECT * FROM courseselections, courseinfo, coursetypecode " +
            "WHERE courseselections.userId = #{userId} AND courseselections.courseId = courseinfo.courseId " +
            "AND courseinfo.courseTypeId = coursetypecode.courseTypeId")
    public List<Map<String, Object>> getCourseList(@Param("userId") Integer userId);

    /**
     * 获取选择该课程的学员列表
     */
    @Select("SELECT * FROM courseselections, usersinfo WHERE courseId = #{courseId} AND courseselections.userId = usersinfo.userId")
    public List<Map<String, Object>> getStudentListByCourseId(@Param("courseId") Integer courseId);


    @Select("SELECT paidCount FROM courseselections where courseId = #{courseId}")
    Integer getCoursePrice(Integer courseId);

    /**
     * 通过课程类型获取学生列表
     */
    @Select("SELECT *\n" +
            "FROM courseinfo c ,coursetypecode c2 ,usersinfo u, courseselections c3 \n" +
            "WHERE c.courseTypeId = c2.courseTypeId AND \n" +
            "        c3.userId = u.userId AND \n" +
            "        c3.courseId = c.courseId AND \n" +
            "        c2.courseTypeId = #{courseTypeId} ORDER BY c.courseName;")
    public List<Map<String, Object>> getStudentListByCourseTypeId(@Param("courseTypeId") Integer courseTypeId);
}
